package com.cpi.cornpp.plants.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.plants.dto.PlantDTO;
import com.cpi.cornpp.plants.exception.PlantException;
import com.cs.common.pool.DBDAO;

/**
 * 
 * @description: This Class is used to handle the Plant Data Access
 * @author Edgar Joao
 */
public class PlantDAO extends DBDAO{
	/** Datasource name*/
	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	/** SQL List Plants */ 
	private static final String SQL_LIST_PLANTS =  "SELECT plant_id, short_name, description, storage_amount, unload_amount, grind_protection_days, priority FROM plants";//$NON-NLS-1$
	/** SQL Insert Plants */ 
	private static final String SQL_INSERT_PLANTS =  "INSERT INTO plants (plant_id, short_name, description, storage_amount, unload_amount, grind_protection_days, priority) VALUES(?,?,?,?,?,?,?)";//$NON-NLS-1$
	/** SQL Delete Plants */ 
	private static final String SQL_DELETE_PLANTS =  "DELETE FROM plants where plant_id = ?";//$NON-NLS-1$
	/** SQL Update Plants */ 
	private static final String SQL_UPDATE_PLANTS =  "UPDATE plants SET short_name = ?, description = ?, storage_amount = ?, unload_amount = ?, grind_protection_days = ?, priority = ? WHERE plant_id = ?";//$NON-NLS-1$
	/** SQL Check Id before Insert Plants */ 
	private static final String SQL_BEFORE_INSERT_PLANTS =  "SELECT plant_id, short_name FROM plants WHERE short_name = upper(?) and plant_id <> ?";//$NON-NLS-1$
	/** SQL get Plant by Id */ 
	private static final String SQL_GET_PLANT_BY_ID =  "SELECT plant_id, short_name, description, storage_amount, unload_amount, grind_protection_days, priority FROM plants WHERE plant_id = ?";//$NON-NLS-1$
	/** SQL order Plant by Id */ 
	private static final String SQL_ORDER_PLANTS_BY_ID = " order by plant_id ";//$NON-NLS-1$
	/**
	 * This method return a list of PlantDTO
	 * @return
	 * @throws Exception
	 */
	public List list(int orderByColumn, boolean ascendingOrder) throws Exception{
		StringBuffer sql = null;
		ArrayList arrayResult = null;
		PreparedStatement preparedStatement =  null;
		PlantDTO versionDTO = null;
		
		sql = new StringBuffer(SQL_LIST_PLANTS);
		sql.append(SQL_ORDER_BY);
		
		switch (orderByColumn){
			case 0: sql.append(PlantDTO.PLANT_ID);
					break;
			case 1: sql.append(PlantDTO.SHORT_NAME);
					break;
			case 2: sql.append(PlantDTO.DESCRIPTION);
					break;
			case 3: sql.append(PlantDTO.STORAGE_AMOUNT);
					break;
			case 4: sql.append(PlantDTO.UNLOAD_AMOUNT);
					break;					
			case 5: sql.append(PlantDTO.GRIND_PROTECTION_DAYS);
					break;
			case 6: sql.append(PlantDTO.PRIORITY);
					break;
		}
		if (ascendingOrder){
			sql.append(SQL_ASCENDING_ORDER);
		} else {
			sql.append(SQL_DESCENDING_ORDER);
		}
		try {
			arrayResult = new ArrayList(0);					
			preparedStatement =  prepareSQL(sql.toString(), DATASOURCE);			
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()){
				versionDTO = new PlantDTO();				
				versionDTO.setPlantId(rst.getInt(1));
				versionDTO.setShortName(rst.getString(2));
				versionDTO.setDescription(rst.getString(3));
				versionDTO.setStorageAmount(rst.getDouble(4));
				versionDTO.setUnloadAmount(rst.getDouble(5));
				versionDTO.setGrindProtectionDays(rst.getInt(6));
				versionDTO.setPriority(rst.getInt(7));
				arrayResult.add(versionDTO);
			}
		}
		catch (SQLException sqlException){
			PlantException plantException = null;
			sqlException.printStackTrace(System.out);
			plantException= new PlantException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw plantException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return arrayResult;
	}
	
	
	/**
	 * This method return a list of PlantDTO
	 * @return
	 * @throws Exception
	 */
	public List list() throws Exception{
		StringBuffer sql = null;
		ArrayList arrayResult = null;
		PreparedStatement preparedStatement =  null;
		PlantDTO plantDTO = null;		
		sql = new StringBuffer(SQL_LIST_PLANTS);		
		sql.append(SQL_ORDER_PLANTS_BY_ID);
		try {
			arrayResult = new ArrayList(0);					
			preparedStatement =  prepareSQL(sql.toString(), DATASOURCE);			
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()){
				plantDTO = new PlantDTO();				
				plantDTO.setPlantId(rst.getInt(1));
				plantDTO.setShortName(rst.getString(2));
				plantDTO.setDescription(rst.getString(3));
				plantDTO.setStorageAmount(rst.getDouble(4));
				plantDTO.setUnloadAmount(rst.getDouble(5));
				plantDTO.setGrindProtectionDays(rst.getInt(6));
				arrayResult.add(plantDTO);
			}
		}
		catch (SQLException sqlException){
			PlantException plantException = null;
			sqlException.printStackTrace(System.out);
			plantException= new PlantException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw plantException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return arrayResult;
	}
	
	/**
	 * Insert Plant
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public boolean insert (PlantDTO entity) throws Exception{		
		PreparedStatement preparedStatement =  null;
		boolean result = false;
		int exInsert = 0;
		try {
			preparedStatement =  prepareSQL(SQL_INSERT_PLANTS, DATASOURCE);
			
			preparedStatement.setInt(1, entity.getPlantId());
			preparedStatement.setString(2, entity.getShortName().toUpperCase().trim());
			preparedStatement.setString(3, entity.getDescription().trim());
			preparedStatement.setDouble(4, entity.getStorageAmount());
			preparedStatement.setDouble(5, entity.getUnloadAmount());
			preparedStatement.setInt(6, entity.getGrindProtectionDays());
			preparedStatement.setInt(7, entity.getPriority());
			
			exInsert = executeInsert(preparedStatement);
			if (exInsert <= 0 ) {
				PlantException plantException = null;
				plantException = new PlantException (MSG_NO_INSERT,CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
				result=false;
				throw plantException;				
			}
			result=true;
		}
		catch (SQLException sqlException){
			PlantException plantException = null;
			sqlException.printStackTrace(System.out);
			plantException = new PlantException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw plantException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
	
	/**
	 * Delete Plant
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public boolean delete(PlantDTO entity) throws Exception {
		PreparedStatement preparedStatement =  null;
		boolean result = false;
		int exDelete = 0;
		try {
			preparedStatement =  prepareSQL(SQL_DELETE_PLANTS, DATASOURCE);
			preparedStatement.setInt(1, entity.getPlantId());
			//preparedStatement.setString(2, entity.getShortName());					
			
			exDelete = executeInsert(preparedStatement);
			if (exDelete <= 0 ) {
				PlantException plantException = null;
				plantException = new PlantException (MSG_NO_DELETE,CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
				result=false;
				throw plantException;				
			}
			result=true;
		}
		catch (SQLException sqlException){
			PlantException plantException = null;
			sqlException.printStackTrace(System.out);
			plantException = new PlantException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			throw plantException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
	/**
	 * Update Plant
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public boolean update(PlantDTO entity) throws Exception {
		PreparedStatement preparedStatement =  null;
		boolean result = false;
		int exDelete = 0;
		try {
			preparedStatement =  prepareSQL(SQL_UPDATE_PLANTS, DATASOURCE);			
			preparedStatement.setString(1, entity.getShortName().toUpperCase().trim());
			preparedStatement.setString(2, entity.getDescription().trim());
			preparedStatement.setDouble(3, entity.getStorageAmount());
			preparedStatement.setDouble(4, entity.getUnloadAmount());
			preparedStatement.setDouble(5, entity.getGrindProtectionDays());
			preparedStatement.setInt(6, entity.getPriority());
			preparedStatement.setInt(7, entity.getPlantId());
			
			exDelete = executeInsert(preparedStatement);
			if (exDelete <= 0 ) {
				PlantException plantException = null;
				plantException = new PlantException (MSG_NO_UPDATE,CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
				result=false;
				throw plantException;				
			}
			result=true;
		}
		catch (SQLException sqlException){
			PlantException plantException = null;
			sqlException.printStackTrace(System.out);
			plantException = new PlantException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw plantException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
	
	/**
	 * Exist Plant Before Insert
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public boolean existPlant(PlantDTO entity) throws Exception {
		PreparedStatement preparedStatement =  null;
		boolean result = false;		
		try {
			preparedStatement =  prepareSQL(SQL_BEFORE_INSERT_PLANTS, DATASOURCE);						
			preparedStatement.setString(1, entity.getShortName());						
			preparedStatement.setInt(2, entity.getPlantId());
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {				
				result=true;							
			}else{
				result=false;
			}
			
		}
		catch (SQLException sqlException){
			PlantException plantException = null;
			sqlException.printStackTrace(System.out);
			plantException = new PlantException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw plantException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
	/**
	 * 
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public PlantDTO getPlant(PlantDTO entity) throws Exception{
		PreparedStatement preparedStatement =  null;
		PlantDTO plantDTO = null;
		try {
			preparedStatement =  prepareSQL(SQL_GET_PLANT_BY_ID, DATASOURCE);						
			preparedStatement.setInt(1, entity.getPlantId());						
			
			ResultSet rst = executeQuery(preparedStatement);
			if (rst.next()) {	
				plantDTO = new PlantDTO();
				plantDTO.setPlantId(rst.getInt(1));
				plantDTO.setShortName(rst.getString(2));
				plantDTO.setDescription(rst.getString(3));
				plantDTO.setStorageAmount(rst.getDouble(4));
				plantDTO.setUnloadAmount(rst.getDouble(5));
				plantDTO.setGrindProtectionDays(rst.getInt(6));
				plantDTO.setPriority(rst.getInt(7));
			}
			
		}
		catch (SQLException sqlException){
			PlantException plantException = null;
			sqlException.printStackTrace(System.out);
			plantException = new PlantException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw plantException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return plantDTO;
	}
}
